-- ----------- --
-- openXDA.sql --
-- ----------- --

ALTER TABLE AccessLog ADD NodeID uniqueidentifier NOT NULL DEFAULT(00000000-0000-0000-0000-000000000000)

ALTER TABLE Setting ADD UNIQUE (Name)
GO

CREATE TABLE CellCarrier
(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NULL,
    Transform VARCHAR(200) NULL
)
GO

DECLARE @DF_FileGroup_Error NVARCHAR(100)
SELECT @DF_FileGroup_Error = CONCAT
(
    'ALTER TABLE FileGroup DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('FileGroup') AND
            sys.columns.name = 'Error'
    )
)
EXECUTE sp_executesql @DF_FileGroup_Error
GO

ALTER TABLE FileGroup
	DROP COLUMN Error
GO

ALTER TABLE FileGroup
ADD ProcessingStatus INT NOT NULL DEFAULT(0)
GO

CREATE TABLE APIAccessKey
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    RegistrationKey VARCHAR(50) NOT NULL UNIQUE,
    APIToken VARCHAR(50) NOT NULL,
    AllowImpersonation BIT NOT NULL DEFAULT 0,
    Expires DATETIME NULL
)
GO

INSERT INTO NodeType VALUES('ScheduledEmail', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Email.ScheduledEmailNode')
GO
INSERT INTO NodeType VALUES('Authorization', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Authentication.AuthenticationProviderNode')
GO
INSERT INTO NodeType VALUES('DataPusher', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.DataPusher.DataPusherNode')
GO
INSERT INTO NodeType VALUES('SSAMS', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.SSAMS.SSAMSNode')
GO
INSERT INTO NodeType VALUES('DatabaseMaintenance', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.DatabaseMaintenance.DatabaseMaintenanceNode')
GO
INSERT INTO NodeType VALUES('Grafana', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Grafana.GrafanaHostingNode')
GO

ALTER TABLE Node ADD AssignedHostRegistrationID INT NULL REFERENCES HostRegistration(ID)
GO

INSERT INTO Node (NodeTypeID, HostRegistrationID, AssignedHostRegistrationID, Name, MinimumHostCount) VALUES((SELECT ID FROM NodeType WHERE TypeName = 'openXDA.Nodes.Types.Email.ScheduledEmailNode'), NULL, NULL, 'Scheduled Emailer', 1)
GO
INSERT INTO Node (NodeTypeID, HostRegistrationID, AssignedHostRegistrationID, Name, MinimumHostCount) VALUES((SELECT ID FROM NodeType WHERE TypeName = 'openXDA.Nodes.Types.Authentication.AuthenticationProviderNode'), NULL, NULL, 'SSO Provider', 1)
GO
INSERT INTO Node (NodeTypeID, HostRegistrationID, AssignedHostRegistrationID, Name, MinimumHostCount) VALUES((SELECT ID FROM NodeType WHERE TypeName = 'openXDA.Nodes.Types.DataPusher.DataPusherNode'), NULL, NULL, 'Data Pusher', 1)
GO

ALTER TABLE Customer ADD
	LSCVS BIT NOT NULL Default(0),
	PQIFacilityID INT NOT NULL Default(-1)
GO

CREATE TABLE CustomerMeter
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID INT NOT NULL FOREIGN KEY REFERENCES Customer(ID),
    MeterID INT NOT NULL FOREIGN KEY REFERENCES Meter(ID)
)
GO

CREATE TABLE GenerationAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID)
)
GO

CREATE TABLE StationAuxAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID)
)
GO

CREATE TABLE StationBatteryAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID)
)
GO

ALTER TABLE BreakerAttributes ADD AirGapResistor BIT NOT NULL DEFAULT(0)
GO

ALTER TABLE LineSegmentAttributes ADD
	FromBus VARCHAR(150) NULL DEFAULT(NULL),
	ToBus VARCHAR(150) NULL DEFAULT(NULL)
GO

-- Generation Model
CREATE VIEW Generation AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN GenerationAttributes ON Asset.ID = GenerationAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Generation ON GENERATION
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Generation') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO GenerationAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Generation ON GENERATION
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Generation Model Triggers 
-- Station Auxilary Model
CREATE VIEW StationAux AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationAuxAttributes ON Asset.ID = StationAuxAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Aux ON STATIONAUX
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationAux') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationAuxAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Aux ON STATIONAUX
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Auxilary Model Triggers 
-- Station Battery Model
CREATE VIEW StationBattery AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationBatteryAttributes ON Asset.ID = StationBatteryAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Battery ON STATIONBATTERY
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationBattery') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationBatteryAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Battery ON STATIONBATTERY
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Bettery Model Triggers
-- Breaker Model 
DROP VIEW Breaker
GO

CREATE VIEW Breaker AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		ThermalRating,
		Speed,
		Description,
		AssetName,
		AssetTypeID,
		TripTime,
		PickupTime,
		TripCoilCondition,
		Spare,
        AirGapResistor
	FROM Asset JOIN BreakerAttributes ON Asset.ID = BreakerAttributes.AssetID
GO

ALTER TRIGGER TR_INSERT_Breaker ON Breaker
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, VoltageKV, AssetName, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Breaker') AS AssetTypeID,
			Description AS Description,
			VoltageKV AS VoltageKV,
			AssetName AS AssetName,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO BreakerAttributes (AssetID, ThermalRating, Speed, TripTime, PickupTime, TripCoilCondition, AirGapResistor)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			ThermalRating AS ThermalRating,
			Speed AS Speed,
			TripTime AS TripTime,
			PickupTime AS PickupTime,
			TripCoilCondition AS TripCoilCondition,
            AirGapResistor AS AirGapResistor
	FROM INSERTED

END
GO

ALTER TRIGGER TR_UPDATE_Breaker ON Breaker
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE BreakerAttributes
		SET
			BreakerAttributes.ThermalRating = INSERTED.ThermalRating,
			BreakerAttributes.Speed = INSERTED.Speed,
			BreakerAttributes.TripTime = INSERTED.TripTime,
			BreakerAttributes.PickupTime = INSERTED.PickupTime,
			BreakerAttributes.TripCoilCondition = INSERTED.TripCoilCondition,
            BreakerAttributes.AirGapResistor = INSERTED.AirGapResistor
		FROM
			BreakerAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = BreakerAttributes.AssetID;
END
GO

-- Line Segment Model 
DROP VIEW LineSegment
GO

CREATE VIEW LineSegment AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		Length,
		R0,
		X0,
		R1,
		X1,
		ThermalRating,
		Description,
		AssetName,
		VoltageKV,
		AssetTypeID,
		Spare,
        IsEnd,
        FromBus,
        ToBus
	FROM Asset JOIN LineSegmentAttributes ON Asset.ID = LineSegmentAttributes.AssetID
GO

ALTER TRIGGER TR_INSERT_LineSegment ON LineSegment
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'LineSegment') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO LineSegmentAttributes (AssetID, Length, R0, X0, R1, X1, ThermalRating, IsEnd, FromBus, ToBus)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
			Length AS Length,
			R0 AS R0,
			X0 AS X0,
			R1 AS R1,
			X1 AS X1,
			ThermalRating AS ThermalRating,
            IsEnd AS IsEnd,
            FromBus AS FromBus,
            ToBus AS ToBus
	FROM INSERTED

END
GO

ALTER TRIGGER TR_UPDATE_LineSegment ON LineSegment
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR Update(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV  = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE LineSegmentAttributes
		SET
			LineSegmentAttributes.R0 = INSERTED.R0,
			LineSegmentAttributes.X0 = INSERTED.X0,
			LineSegmentAttributes.R1 = INSERTED.R1,
			LineSegmentAttributes.X1 = INSERTED.X1,
            LineSegmentAttributes.Length = INSERTED.Length,
			LineSegmentAttributes.ThermalRating = INSERTED.ThermalRating,
            LineSegmentAttributes.IsEnd = INSERTED.IsEnd,
            LineSegmentAttributes.FromBus = INSERTED.FromBus,
            LineSegmentAttributes.ToBus = INSERTED.ToBus
		FROM
			LineSegmentAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = LineSegmentAttributes.AssetID;
END
GO

ALTER TABLE AssetLocation
ADD CONSTRAINT UC_AssetLocation UNIQUE(AssetID, LocationID)
GO

DECLARE @FK_PQMeasurement_UnitID NVARCHAR(100)
SELECT @FK_PQMeasurement_UnitID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'UnitID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_UnitID
GO

DECLARE @FK_PQMeasurement_PhaseID NVARCHAR(100)
SELECT @FK_PQMeasurement_PhaseID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'PhaseID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_PhaseID
GO

DECLARE @FK_PQMeasurement_HarmonicGroup NVARCHAR(100)
SELECT @FK_PQMeasurement_HarmonicGroup = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'HarmonicGroup' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_HarmonicGroup
GO

DECLARE @FK_PQMeasurement_Enabled NVARCHAR(100)
SELECT @FK_PQMeasurement_Enabled = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'Enabled' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_Enabled
GO

DECLARE @DF_PQMeasurement_HarmonicGroup NVARCHAR(100)
SELECT @DF_PQMeasurement_HarmonicGroup = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'HarmonicGroup'
    )
)
EXECUTE sp_executesql @DF_PQMeasurement_HarmonicGroup
GO

DECLARE @DF_PQMeasurement_Enabled NVARCHAR(100)
SELECT @DF_PQMeasurement_Enabled = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'Enabled'
    )
)
EXECUTE sp_executesql @DF_PQMeasurement_Enabled
GO

ALTER TABLE PQMeasurement DROP COLUMN
	UnitID,
	PhaseID,
	HarmonicGroup,
	Enabled
GO

ALTER TABLE PQMeasurement ADD Unit VARCHAR(200) NOT NULL DEFAULT('None')
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementTypeID
ON PQMeasurement(MeasurementTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementCharacteristicID
ON PQMeasurement(MeasurementCharacteristicID ASC)
GO

ALTER TABLE Channel
ADD Trend bit NOT NULL DEFAULT 0
GO

ALTER TABLE ChannelGroupType
ADD Unit VARCHAR(200) NOT NULL Default('None')
GO

UPDATE ChannelGroupType SET Unit = (SELECT Name FROM Unit WHERE Unit.ID = ChannelGroupType.UnitID)
GO

ALTER TABLE ChannelGroupType
DROP COLUMN UnitID
GO

ALTER TABLE AssetGroup
ADD DisplayEmail Bit NOT NULL Default(0)
GO

ALTER TABLE AssetGroupAssetGroup
ADD CONSTRAINT not_equal check (ChildAssetGroupID <> ParentAssetGroupID)
GO

INSERT INTO DataOperation(AssemblyName, TypeName, LoadOrder) VALUES('FaultData.dll', 'FaultData.DataOperations.LSCVSDataOperation', 15)
GO

UPDATE AssetGroup SET DisplayEmail = 1 WHERE Name = 'AllAssets'
GO

--Security
INSERT INTO ApplicationNode VALUES ('00000000-0000-0000-0000-000000000000','OpenXDA')
GO

CREATE TABLE ApplicationSustainedUser (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ApplicationNodeID  UNIQUEIDENTIFIER NOT NULL REFERENCES ApplicationNode(ID),
    UserAccountID  UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    Code VARCHAR(200) NOT NULL
)
GO


--Email
ALTER TABLE EmailCategory
ADD SelfSubscribe BIT NOT NULL Default 1
GO

ALTER TABLE EmailType ADD
	Name VARCHAR(100) NOT NULL DEFAULT('Email Template'),
    Template VARCHAR(MAX) NOT NULL DEFAULT('Email Template'),
    TriggerEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
    CombineEventsSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT ID FROM Event WHERE ID = {0}',
    MinDelay FLOAT NOT NULL DEFAULT 10,
    MaxDelay FLOAT NOT NULL DEFAULT 60,
    ShowSubscription BIT NOT NULL DEFAULT 1,
    RequireApproval BIT NOT NULL DEFAULT 0,
    FilePath VARCHAR(200) NULL DEFAULT NULL
GO

CREATE TABLE ScheduledEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EmailCategoryID INT NOT NULL REFERENCES EmailCategory(ID),
    Name VARCHAR(100) NOT NULL,
    Schedule VARCHAR(100) NOT NULL,
    Template VARCHAR(MAX) NOT NULL,
    TriggerEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
    SMS BIT NOT NULL DEFAULT 0,
    FilePath VARCHAR(200) NULL DEFAULT NULL
)
GO

ALTER TABLE UserAccountEmailType ADD
	AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID) DEFAULT 1,
	Approved BIT NOT NULL DEFAULT 0
GO

CREATE VIEW SubscribeEmails
AS
SELECT
    UserAccountEmailType.ID,
    UserAccountEmailType.Approved,
    EmailType.ID as EmailID,
    UserAccount.FirstName as FirstName,
    UserAccount.LastName as LastName,
    UserAccount.Email as Email,
    AssetGroup.Name as AssetGroup
FROM
    UserAccountEmailType JOIN
    EmailType ON EmailType.ID = UserAccountEmailType.EmailTypeID JOIN
    UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID JOIN
    AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID
GO

CREATE TABLE UserAccountScheduledEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    ScheduledEmailTypeID INT NOT NULL REFERENCES ScheduledEmailType(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
    Approved BIT NOT NULL DEFAULT 0
)
GO

CREATE TABLE TriggeredEmailDataSource
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL UNIQUE,
    AssemblyName VARCHAR(200) NOT NULL,
    TypeName VARCHAR(200) NOT NULL,
    ConfigUI VARCHAR(200) NOT NULL
)
GO

CREATE VIEW SubscribeScheduledEmails
AS
SELECT
    UserAccountScheduledEmailType.ID,
    UserAccountScheduledEmailType.Approved,
    ScheduledEmailType.ID as ScheduledEmailID,
    UserAccount.FirstName as FirstName,
    UserAccount.LastName as LastName,
    UserAccount.Email as Email,
    AssetGroup.Name as AssetGroup
FROM
    UserAccountScheduledEmailType JOIN
    ScheduledEmailType ON ScheduledEmailType.ID = UserAccountScheduledEmailType.ScheduledEmailTypeID JOIN
    UserAccount ON UserAccount.ID = UserAccountScheduledEmailType.UserAccountID JOIN
    AssetGroup ON AssetGroup.ID = UserAccountScheduledEmailType.AssetGroupID
GO

CREATE TABLE TriggeredEmailDataSourceEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EmailTypeID INT NOT NULL REFERENCES EmailType(ID),
    TriggeredEmailDataSourceID INT NOT NULL REFERENCES TriggeredEmailDataSource(ID),
)
GO

CREATE TABLE ScheduledEmailDataSource
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL UNIQUE,
    AssemblyName VARCHAR(200) NOT NULL,
    TypeName VARCHAR(200) NOT NULL,
    ConfigUI VARCHAR(200) NOT NULL
)
GO

CREATE TABLE ScheduledEmailDataSourceEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ScheduledEmailTypeID INT NOT NULL REFERENCES ScheduledEmailType(ID),
    ScheduledEmailDataSourceID INT NOT NULL REFERENCES ScheduledEmailDataSource(ID),
)
GO

CREATE TABLE TriggeredEmailDataSourceSetting
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    TriggeredEmailDataSourceEmailTypeID INT NOT NULL REFERENCES TriggeredEmailDataSourceEmailType(ID),
    Name VARCHAR(200) NOT NULL,
    Value VARCHAR(MAX) NOT NULL
)
GO

CREATE TABLE ScheduledEmailDataSourceSetting
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ScheduledEmailDataSourceEmailTypeID INT NOT NULL REFERENCES ScheduledEmailDataSourceEmailType(ID),
    Name VARCHAR(200) NOT NULL,
    Value VARCHAR(MAX) NOT NULL
)
GO

ALTER TABLE SentEmail
ADD EmailTypeID INT NOT NULL DEFAULT(1)
GO

CREATE TABLE ChannelTemplateFile
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    FileBlob VARBINARY(MAX) NOT NULL,
    FileName VARCHAR(MAX) NOT NULL,
    ShowTrend BIT NOT NULL DEFAULT(1),
    ShowEvents BIT NOT NULL DEFAULT(1),
    SortOrder INT NOT NULL DEFAULT(0)
)
GO

CREATE TABLE UserAccountCarrier
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    CarrierID INT NOT NULL REFERENCES CellCarrier(ID),
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID)
)
GO

--Events
ALTER TABLE ChannelData
ADD CONSTRAINT UC_ChannelData UNIQUE(SeriesID, EventID)
GO
ALTER TABLE ChannelData
DROP COLUMN FileGroupID
GO
ALTER TABLE ChannelData
DROP COLUMN RuntimeID
GO
ALTER TABLE ChannelData
DROP COLUMN EventDataID
GO

CREATE NONCLUSTERED INDEX IX_ChannelData_SeriesID
ON ChannelData(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_ChannelData_EventID
ON ChannelData(EventID ASC)
GO

ALTER TABLE EventType ADD
	ShowInFilter BIT NOT NULL Default(1),
	Category VARCHAR(200) NULL
GO

CREATE TABLE EventTypeAssetType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventTypeID INT NOT NULL REFERENCES EventType(ID),
    AssetTypeID INT NOT NULL REFERENCES AssetType(ID),
    CONSTRAINT UC_EventTypeAssetType UNIQUE(EventTypeID, AssetTypeID)
)
GO

Create Table EventWorstDisturbance (
 ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 EventID INT NOT NULL REFERENCES Event(ID),
 WorstDisturbanceID INT NOT NULL REFERENCES Disturbance(ID),
 WorstLLDisturbanceID INT NULL REFERENCES Disturbance(ID),
 WorstLNDisturbanceID  INT NULL REFERENCES Disturbance(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_EventWorstDisturbance_EventID
ON EventWorstDisturbance(EventID)
GO

CREATE NONCLUSTERED INDEX IX_EventWorstDisturbance_WorstDisturbanceID
ON EventWorstDisturbance(WorstDisturbanceID)
GO

CREATE NONCLUSTERED INDEX IX_EventWorstDisturbance_WorstLLDisturbanceID
ON EventWorstDisturbance(WorstLLDisturbanceID)
GO

CREATE NONCLUSTERED INDEX IX_EventWorstDisturbance_WorstLNDisturbanceID
ON EventWorstDisturbance(WorstLNDisturbanceID)
GO

CREATE TABLE MATLABAnalytic
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssemblyName VARCHAR(MAX) NOT NULL,
    MethodName VARCHAR(MAX) NOT NULL,
    SettingSQL VARCHAR(MAX) NOT NULL DEFAULT '',
    LoadOrder INT NOT NULL DEFAULT 0
)
GO

CREATE TABLE MATLABAnalyticAssetType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MATLABAnalyticID INT NOT NULL REFERENCES MATLABAnalytic(ID),
    AssetTypeID INT NOT NULL REFERENCES AssetType(ID)
)
GO

CREATE TABLE MATLABAnalyticEventType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MATLABAnalyticID INT NOT NULL REFERENCES MATLABAnalytic(ID),
    EventTypeID INT NOT NULL REFERENCES EventType(ID)
)
GO

CREATE TABLE EventTag
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL UNIQUE,
    Description VARCHAR(MAX) NULL,
    ShowInFilter BIT NOT NULL Default(1),
)
GO

CREATE TABLE EventEventTag
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    EventTagID INT NOT NULL REFERENCES EventTag(ID),
    TagData VARCHAR(MAX) NULL
)
GO

--Fault Location
CREATE TABLE MeterDependentAssetDesignation
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterAssetID INT NOT NULL REFERENCES MeterAsset(ID),
    Designation VARCHAR(200) NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_MeterDependentAssetDesignation_MeterAssetID
ON MeterDependentAssetDesignation(MeterAssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultDetectionLogic_MeterAssetID
ON FaultDetectionLogic(MeterAssetID ASC)
GO

ALTER TABLE FaultCurve
ADD PathNumber INT NOT NULL DEFAULT(0)
GO

ALTER TABLE FaultSummary ADD
	PathNumber INT NOT NULL DEFAULT(0),
	LineSegmentID INT NOT NULL REFERENCES Asset(ID) DEFAULT(0),
	LineSegmentDistance FLOAT NOT NULL DEFAULT(0)
GO

CREATE TABLE VaisalaExtendedLightningData
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LightningStrikeID INT NOT NULL REFERENCES LightningStrike(ID),
    PeakCurrent SMALLINT NOT NULL,
    FlashMultiplicity TINYINT NOT NULL,
    ParticipatingSensors TINYINT NOT NULL,
    DegreesOfFreedom TINYINT NOT NULL,
    EllipseAngle FLOAT NOT NULL,
    SemiMajorAxisLength FLOAT NOT NULL,
    SemiMinorAxisLength FLOAT NOT NULL,
    ChiSquared FLOAT NOT NULL,
    Risetime FLOAT NOT NULL,
    PeakToZeroTime FLOAT NOT NULL,
    MaximumRateOfRise FLOAT NOT NULL,
    CloudIndicator BIT NOT NULL,
    AngleIndicator BIT NOT NULL,
    SignalIndicator BIT NOT NULL,
    TimingIndicator BIT NOT NULL
)
GO

----- ALARMS -----
CREATE NONCLUSTERED INDEX IX_AlarmGroup_AlarmTypeID
ON AlarmGroup(AlarmTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmGroup_SeverityID
ON AlarmGroup(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_AlarmGroupID
ON Alarm(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_SeriesID
ON Alarm(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_AlarmGroupID
ON AlarmFactor(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_SeverityID
ON AlarmFactor(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmValue_AlarmID
ON AlarmValue(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmValue_AlarmDayID
ON AlarmValue(AlarmDayID ASC)
GO

ALTER TABLE AlarmLog ADD SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID) DEFAULT(4)
GO

CREATE TABLE LatestAlarmLog
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AlarmID INT NOT NULL UNIQUE REFERENCES Alarm(ID),
    AlarmLogID INT NOT NULL REFERENCES AlarmLog(ID),
    SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID),
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NULL
)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmID
ON LatestAlarmLog(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmLogID
ON LatestAlarmLog(AlarmLogID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_SeverityID
ON LatestAlarmLog(SeverityID ASC)
GO

CREATE TRIGGER AlarmLog_UpdateLatestAlarmLog
    ON AlarmLog
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Update existing records in LatestAlarmLog
    UPDATE LatestAlarmLog
    SET
        AlarmLogID = inserted.ID,
        SeverityID = inserted.SeverityID,
        StartTime = inserted.StartTime,
        EndTime = inserted.EndTime
    FROM
        LatestAlarmLog JOIN
        inserted ON inserted.AlarmID = LatestAlarmLog.AlarmID JOIN
        AlarmLog ON LatestAlarmLog.AlarmLogID = AlarmLog.ID
    WHERE inserted.StartTime >= AlarmLog.StartTime

    -- Determine which alarms have never had logs before now
    SELECT DISTINCT inserted.AlarmID
    INTO #alarm
    FROM
        inserted LEFT OUTER JOIN
        LatestAlarmLog ON inserted.AlarmID = LatestAlarmLog.AlarmID
    WHERE LatestAlarmLog.ID IS NULL

    -- Insert new records in LatestAlarmLog
    -- for alarms that never had logs before now
    INSERT INTO LatestAlarmLog(AlarmID, AlarmLogID, SeverityID, StartTime, EndTime)
    SELECT
        AlarmLog.AlarmID,
        AlarmLog.ID AlarmLogID,
        AlarmLog.SeverityID,
        AlarmLog.StartTime,
        AlarmLog.EndTime
    FROM
        #alarm CROSS APPLY
        (
            SELECT TOP 1 *
            FROM inserted
            WHERE AlarmID = #alarm.AlarmID
            ORDER BY StartTime DESC, ID DESC
        ) AlarmLog
END
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayID
ON AlarmDayGroupAlarmDay(AlarmDayID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayGroupID
ON AlarmDayGroupAlarmDay(AlarmDayGroupID ASC)
GO

DROP VIEW AlarmGroupView
GO

CREATE VIEW AlarmGroupView AS 
SELECT 
	AlarmGroup.ID,
	AlarmGroup.Name,
	AlarmSeverity.Name AlarmSeverity,
	CountStats.ChannelCount Channels,
	CountStats.MeterCount Meters,
	LastAlarm.StartTime LastAlarmStart,
	LastAlarm.EndTime LastAlarmEnd,
	LastAlarm.ChannelName LastChannel,
	LastAlarm.MeterName LastMeter,
	AlarmType.Description AS AlarmType
FROM 
	AlarmGroup LEFT JOIN
	AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN
	AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT Channel.ID) ChannelCount,
            COUNT(DISTINCT Channel.MeterID) MeterCount
        FROM
            Channel JOIN
            Series ON Series.ChannelID = Channel.ID JOIN
            Alarm ON Alarm.SeriesID = Series.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
    ) CountStats OUTER APPLY
    (
        SELECT TOP 1
            LatestAlarmLog.StartTime,
            LatestAlarmLog.EndTime,
            Channel.Name ChannelName,
            Meter.Name MeterName
        FROM
            Alarm JOIN
            Series ON Alarm.SeriesID = Series.ID JOIN
            Channel ON Series.ChannelID = Channel.ID JOIN
            Meter ON Channel.MeterID = Meter.ID JOIN
            LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
        ORDER BY
            LatestAlarmLog.StartTime DESC,
            LatestAlarmLog.AlarmLogID DESC
    ) LastAlarm
GO

DROP VIEW ActiveAlarmView
GO

CREATE VIEW ActiveAlarmView AS
SELECT
    Alarm.ID AS AlarmID,
    Alarm.AlarmGroupID AS AlarmGroupID,
    AlarmGroup.AlarmTypeID AS AlarmTypeID,
    AlarmFactor.ID AS AlarmFactorID,
    AlarmFactor.SeverityID,
    Alarm.SeriesID AS SeriesID,
    AlarmFactor.Factor AS Value
FROM
    (
        SELECT ID, Factor, AlarmGroupID, SeverityID
        FROM AlarmFactor
        UNION
        SELECT
            NULL AS ID,
            1.0 AS Factor,
            AlarmGroup.ID AS AlarmGroupID,
            AlarmGroup.SeverityID
        FROM AlarmGroup
    ) AlarmFactor LEFT JOIN
    Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN
    AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID
GO

ALTER TABLE NoteType ADD Label VARCHAR(200)
GO

UPDATE NoteType SET Label = 'Meter' WHERE Name = 'Meter'
GO

UPDATE NoteType SET Label = 'Event' WHERE Name = 'Event'
GO

UPDATE NoteType SET Label = 'Asset' WHERE Name = 'Asset'
GO

UPDATE NoteType SET Label = 'Substation' WHERE Name = 'Location'
GO

UPDATE NoteType SET Label = 'Customer' WHERE Name = 'Customer'
GO

UPDATE NoteType SET Label = 'User' WHERE Name = 'User'
GO

INSERT INTO [NoteApplication] (Name) VALUES ('SEbrowser')
GO

ALTER TABLE MetersToDataPush ADD RemoteXDAInstanceID INT NOT NULL FOREIGN KEY REFERENCES [RemoteXDAInstance](ID) DEFAULT(1)
GO
ALTER TABLE MetersToDataPush ADD CONSTRAINT UC_MetersToDataPush_RemoteXDAInstanceID_LocalXDAMeterID UNIQUE(RemoteXDAInstanceID, LocalXDAMeterID)
GO

ALTER TABLE AssetsToDataPush ADD
	RemoteXDAInstanceID INT NOT NULL FOREIGN KEY REFERENCES [RemoteXDAInstance](ID) DEFAULT(1),
	Obsfucate bit NOT NULL,
	Synced bit NOT NULL
GO
ALTER TABLE AssetsToDataPush ADD CONSTRAINT UC_AssetsToDataPush_RemoteXDAInstanceID_LocalXDAAssetID UNIQUE(RemoteXDAInstanceID, LocalXDAAssetID)
GO

----- VIEWS -----
CREATE VIEW ActiveSubscription AS
	SELECT 
		UserAccountEmailType.ID AS UserAccountEmailID,
		UserAccountEmailType.UserAccountID AS UserAccountID,
		UserAccountEmailType.Approved AS Approved,
		AssetGroup.Name AS AssetGroup,
		EmailType.Name AS EmailName,
		EmailCategory.Name AS Category,
		EmailType.ID AS EmailTypeID,
		SentEmail.Subject AS Subject,
		SentEmail.TimeSent AS LastSent,
        UserAccount.Name AS UserName,
		UserAccount.Email AS Email
	FROM UserAccountEmailType LEFT JOIN
		AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID LEFT JOIN
		EmailType ON UserAccountEmailType.EmailTypeID = EmailType.ID LEFT JOIN
		EmailCategory ON EmailCategory.ID = EmailType.EmailCategoryID LEFT JOIN
		SentEmail ON SentEmail.EmailTypeID = EmailType.ID  LEFT JOIN
		UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID
GO

CREATE VIEW ActiveScheduledSubscription AS
    SELECT 
        UserAccountScheduledEmailType.ID AS UserAccountScheduledEmailID,
        UserAccountScheduledEmailType.UserAccountID AS UserAccountID,
        AssetGroup.Name AS AssetGroup,
        ScheduledEmailType.Name AS EmailName,
        EmailCategory.Name AS Category,
        ScheduledEmailType.ID AS ScheduledEmailTypeID,
        UserAccount.Name AS UserName,
        UserAccount.Email AS Email
    FROM UserAccountScheduledEmailType LEFT JOIN
        AssetGroup ON AssetGroup.ID = UserAccountScheduledEmailType.AssetGroupID LEFT JOIN
        ScheduledEmailType ON UserAccountScheduledEmailType.ScheduledEmailTypeID = ScheduledEmailType.ID LEFT JOIN
        EmailCategory ON EmailCategory.ID = ScheduledEmailType.EmailCategoryID LEFT JOIN
        UserAccount ON UserAccount.ID = UserAccountScheduledEmailType.UserAccountID
GO

CREATE VIEW CustomerMeterDetail AS
SELECT
    CustomerMeter.ID AS ID,
    Customer.CustomerKey AS CustomerKey,
    Customer.Name AS CustomerName,
    Meter.AssetKey AS MeterKey,
    Meter.Name AS MeterName,
    Location.Name AS MeterLocation,
    Customer.ID AS CustomerID,
    Meter.ID AS MeterID
FROM
    CustomerMeter LEFT JOIN Meter ON Meter.ID = CustomerMeter.MeterID LEFT OUTER JOIN
    Customer ON Customer.ID = CustomerMeter.CustomerID LEFT OUTER JOIN
    Location ON Meter.LocationID = Location.ID
GO

CREATE VIEW  MeterFacility AS
(
    SELECT
        CustomerMeter.ID AS ID,
        CustomerMeter.MeterID AS MeterID,
        Customer.PQIFacilityID AS FacilityID
    FROM
        Customer JOIN
        CustomerMeter ON CustomerMeter.CustomerID = Customer.ID
)
UNION
(
    SELECT
        CustomerAsset.ID AS ID,
        MeterAsset.MeterID AS MeterID,
        Customer.PQIFacilityID AS FacilityID
    FROM
        Customer JOIN
        CustomerAsset ON CustomerAsset.CustomerID = Customer.ID LEFT OUTER JOIN
        MeterAsset ON MeterAsset.AssetID = CustomerAsset.AssetID
)
GO

DROP VIEW AssetGroupView
GO

CREATE VIEW AssetGroupView AS
SELECT
    AssetGroup.ID,
    AssetGroup.Name,
    AssetGroup.DisplayDashboard,
    AssetGroup.DisplayEmail,
    AssetGroupAssetGroup.Count AS AssetGroups,
    MeterAssetGroup.Count AS Meters,
    AssetAssetGroup.Count AS Assets,
    UserAccountAssetGroup.Count AS Users
FROM
    AssetGroup OUTER APPLY
    (SELECT COUNT(*) FROM AssetGroupAssetGroup WHERE AssetGroup.ID = AssetGroupAssetGroup.ParentAssetGroupID) AssetGroupAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM MeterAssetGroup WHERE AssetGroup.ID = MeterAssetGroup.AssetGroupID) MeterAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM AssetAssetGroup WHERE AssetGroup.ID = AssetAssetGroup.AssetGroupID) AssetAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM UserAccountAssetGroup WHERE AssetGroup.ID = UserAccountAssetGroup.AssetGroupID) UserAccountAssetGroup(Count)
GO

DROP VIEW MeterAssetGroupView
GO

CREATE VIEW MeterAssetGroupView
AS
SELECT
    MeterAssetGroup.ID,
    Meter.Name AS MeterName,
    Meter.ID AS MeterID,
    AssetGroupID,
    Location.Name AS Location,
    AssetGroup.Name,
    AssetGroup.DisplayDashboard
FROM
    MeterAssetGroup JOIN
    Meter ON MeterAssetGroup.MeterID = Meter.ID JOIN
    AssetGroup ON MeterAssetGroup.AssetGroupID = AssetGroup.ID JOIN
    Location ON Meter.LocationID = Location.ID
GO

DROP VIEW AssetAssetGroupView
GO

CREATE VIEW AssetAssetGroupView
 AS
 SELECT
    AssetAssetGroup.ID,
    Asset.AssetKey AS AssetName,
    Asset.AssetName AS LongAssetName,
    Asset.ID AS AssetID,
	AssetType.Name AS AssetType,
	(SELECT Top 1 LocationKey FROM Location WHERE Location.ID IN (SELECT LocationID FROM AssetLocation WHERE AssetLocation.AssetID = Asset.ID)) AS AssetLocation,
    AssetGroupID,
    AssetGroup.Name,
    AssetGroup.DisplayDashboard
 FROM
    AssetAssetGroup JOIN
    Asset ON AssetAssetGroup.AssetID = Asset.ID LEFT JOIN
    AssetGroup ON AssetAssetGroup.AssetGroupID = AssetGroup.ID JOIN
    AssetType ON Asset.AssetTypeID = AssetType.ID
GO

DROP VIEW UserAccountAssetGroupView
GO

CREATE VIEW UserAccountAssetGroupView
AS
SELECT
    UserAccountAssetGroup.ID,
    UserAccountAssetGroup.UserAccountID,
    UserAccountAssetGroup.AssetGroupID,
    UserAccount.Name AS Username,
    AssetGroup.Name AS GroupName
FROM
    UserAccountAssetGroup JOIN
    UserAccount ON UserAccountAssetGroup.UserAccountID = UserAccount.ID JOIN
    AssetGroup ON UserAccountAssetGroup.AssetGroupID = AssetGroup.ID
GO

CREATE VIEW TriggeredEmailDataSourceEmailTypeView 
AS
SELECT 
    TriggeredEmailDataSourceEmailType.*,
    TriggeredEmailDataSource.Name AS TriggeredEmailDataSourceName
FROM
    TriggeredEmailDataSourceEmailType LEFT JOIN 
    TriggeredEmailDataSource ON TriggeredEmailDataSourceEmailType.TriggeredEmailDataSourceID = TriggeredEmailDataSource.ID
GO

CREATE VIEW ScheduledEmailDataSourceEmailTypeView 
AS
SELECT 
    ScheduledEmailDataSourceEmailType.*,
    ScheduledEmailDatasource.Name AS ScheduledEmailDataSourceName
FROM
    ScheduledEmailDataSourceEmailType LEFT JOIN 
    ScheduledEmailDatasource ON ScheduledEmailDataSourceEmailType.ScheduledEmailDataSourceID = ScheduledEmailDatasource.ID
GO

DROP VIEW EmailTypeView
GO

CREATE VIEW EmailTypeView
AS
SELECT
    EmailType.ID,
    EmailType.EmailCategoryID,
    EmailCategory.Name AS EmailCategory,
    EmailType.Name,
    EmailType.Template,
    EmailType.TriggerEmailSQL,
    EmailType.CombineEventsSQL,
    EmailType.MinDelay,
    EmailType.MaxDelay,
    EmailType.SMS,
    EmailType.ShowSubscription,
    EmailType.RequireApproval,
    EmailType.FilePath
FROM
    EmailType JOIN
    EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID
GO

DROP FUNCTION RecursiveMeterSearch
GO

CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int)
RETURNS TABLE
AS
RETURN
    WITH AssetGroupHierarchy AS
    (
        SELECT
            AssetGroupAssetGroup.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            1 Depth
        FROM AssetGroupAssetGroup
        WHERE AssetGroupAssetGroup.ParentAssetGroupID = @assetGroupID
        UNION ALL
        SELECT
            AssetGroupHierarchy.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            AssetGroupHierarchy.Depth + 1 Depth
        FROM
            AssetGroupHierarchy JOIN
            AssetGroupAssetGroup ON AssetGroupHierarchy.ChildAssetGroupID = AssetGroupAssetGroup.ParentAssetGroupID
        WHERE AssetGroupHierarchy.Depth < 10
    )
    SELECT DISTINCT MeterID AS ID
    FROM
        MeterAssetGroup LEFT JOIN
        AssetGroupHierarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHierarchy.ChildAssetGroupID
    WHERE
        MeterAssetGroup.AssetGroupID = @assetGroupID OR
        MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHierarchy)
GO

ALTER TABLE StandardMagDurCurve Add
	Color VARCHAR(255) NOT NULL DEFAULT('#007a29')
GO


-- ------------------- --
-- DefaultSettings.sql --
-- ------------------- --
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('COMTRADE.Root2AdjustmentQuery', 'SELECT ID FROM Meter WHERE Make = ''SEL''', 'SELECT ID FROM Meter WHERE Make = ''SEL''')
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Email.MinimumChartSamplesPerCycle', '-1', '-1'
WHERE 'Email.MinimumChartSamplesPerCycle' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AdminUser', 'admin', 'admin'
WHERE 'Grafana.AdminUser' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AuthProxyHeaderName', 'X-WEBAUTH-USER', 'X-WEBAUTH-USER'
WHERE 'Grafana.AuthProxyHeaderName' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.BasePath', 'Grafana', 'Grafana'
WHERE 'Grafana.BasePath' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.HostedURL', 'http://localhost:8185', 'http://localhost:8185'
WHERE 'Grafana.HostedURL' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.InitializationTimeout', '30', '30'
WHERE 'Grafana.InitializationTimeout' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.LastDashboardCookieName', 'x-last-dashboard', 'x-last-dashboard'
WHERE 'Grafana.LastDashboardCookieName' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.OrganizationID', '1', '1'
WHERE 'Grafana.OrganizationID' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.ServerPath', 'Grafana\bin\grafana-server.exe', 'Grafana\bin\grafana-server.exe'
WHERE 'Grafana.ServerPath' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Subscription.ConfirmSubject', 'OpenXDA Confirm Email', 'OpenXDA Confirm Email'
WHERE 'Subscription.ConfirmSubject' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Subscription.ConfirmTemplate', 'Please click the following Link to confirm your email address \n http://localhost/SystemCenterNotification/ConfirmEmail', 'Please click the following Link to confirm your email address \n http://localhost/SystemCenterNotification/ConfirmEmail'
WHERE 'Subscription.ConfirmTemplate' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Subscription.RequireConfirmation', 'true', 'true'
WHERE 'Subscription.RequireConfirmation' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'EventEmail.RestorationURL', 'http://localhost:8989/RestoreEventEmail.cshtml', 'http://localhost:8989/RestoreEventEmail.cshtml'
WHERE 'EventEmail.RestorationURL' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'FileProcessor.FileGroupingPattern', '^(?<FileGroupKey>.*)\.[^\.]*$', '^(?<FileGroupKey>.*)\.[^\.]*$'
WHERE 'FileProcessor.FileGroupingPattern' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.HistogramBucket', 'histogram_bucket', 'histogram_bucket'
WHERE 'HIDS.HistogramBucket' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.Host', '', ''
WHERE 'HIDS.Host' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.PointBucket', 'point_bucket', 'point_bucket'
WHERE 'HIDS.PointBucket' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.OrganizationID', 'gpa', 'gpa'
WHERE 'HIDS.OrganizationID' NOT IN (SELECT Name FROM Setting)
GO
INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.TokenID', '', ''
WHERE 'HIDS.TokenID' NOT IN (SELECT Name FROM Setting)
GO

-- Standard Email Datasource Types --
INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('SQL','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.SQLDataSource','sql')
GO

INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('PQI','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.PQIDataSource','pqi')
GO

INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('FTT','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.FaultTraceTool.FTTDataSource','ftt')
GO

-- Scheduled Email Datasource Types --
INSERT INTO ScheduledEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('SQL','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.SQLDataSource','sql')
GO

INSERT INTO ScheduledEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('AZURE','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.AzureDataSource','azure')
GO

INSERT INTO ScheduledEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('HIDS','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.HIDSDataSource','hids')
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Enabled'
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Frequency'
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Verbose'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Enabled'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Frequency'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Verbose'
GO

UPDATE StandardMagDurCurve
SET Color = '#007a29'
WHERE Name LIKE 'ITIC'
GO

UPDATE StandardMagDurCurve
SET Color = '#edc240'
WHERE Name LIKE 'SEMI F47'
GO

UPDATE StandardMagDurCurve
SET Color = '#a30000'
WHERE Name LIKE 'IEEE 1668 Type I & II'
GO

UPDATE StandardMagDurCurve
SET Color = '#185aa9'
WHERE Name LIKE 'IEEE 1668 Type III'
GO

INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'NERC PRC-024-2', NULL, '#d3d3d3')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Transients', NULL, '#afd8f8')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Sag', NULL, '#f47d23')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Swell', NULL, '#008c48')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Mom. Interruption', NULL, '#ee2e2f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Sag', NULL, '#737373')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Swell', NULL, '#662c91')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temp. Interruption', NULL, '#bd9b33')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Sag', NULL, '#ff904f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Swell', NULL, '#ff9999')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Sustained Int.', NULL, '#0029A3')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Undervoltage', NULL, '#cb4b4b')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Overvoltage', NULL, '#4da74d')
GO

UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.001 1.2, 0.2 1.2, 0.2 1.175, 0.5 1.175,0.5 1.15,1 1.15, 1 1.10,4 1.10, 4 0.9, 3 0.9, 3 0.75, 2 0.75, 2 0.65, 0.3 0.65, 0.3 0.45, 0.15 0.45, 0.15 0, 0.001 0, 0.001 1.2))' WHERE Name = 'NERC PRC-024-2'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.000001 0, 0.01 0, 0.01 5.0, 0.000001 5.0, 0.000001 0))' WHERE Name = 'IEEE 1159 Transients'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.1, 0.5 0.1, 0.5 0.9, 0.01 0.9, 0.01 0.1))' WHERE Name = 'IEEE 1159 Instantaneous Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 1.1, 0.5 1.1, 0.5 1.8, 0.01 1.8, 0.01 1.1))' WHERE Name = 'IEEE 1159 Instantaneous Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0, 3.0 0, 3.0 0.1, 0.01 0.1, 0.01 0))' WHERE Name = 'IEEE 1159 Mom. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 0.1, 3.0 0.1, 3 0.9, 0.5 0.9, 0.5 0.1))' WHERE Name = 'IEEE 1159 Momentary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 1.1, 3.0 1.1, 3.0 1.4, 0.5 1.4, 0.5 1.1))' WHERE Name = 'IEEE 1159 Momentary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0, 60.0 0, 60.0 0.1, 3.0 0.1, 3.0 0))' WHERE Name = 'IEEE 1159 Temp. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0.1, 60.0 0.1, 60.0 0.9, 3.0 0.9, 3.0 0.1))' WHERE Name = 'IEEE 1159 Temporary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 1.1, 60.0 1.1, 60.0 1.2, 3.0 1.2, 3.0 1.1))' WHERE Name = 'IEEE 1159 Temporary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0, 1000.0 0, 1000.0 0.1, 60.0 0.1, 60.0 0))' WHERE Name = 'IEEE 1159 Sustained Int.'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0.8, 1000.0 0.8, 1000.0 0.9, 60.0 0.9, 60.0 0.8))' WHERE Name = 'IEEE 1159 Undervoltage'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 1.1, 1000.0 1.1, 1000.0 1.2, 60.0 1.2, 60.0 1.1))' WHERE Name = 'IEEE 1159 Overvoltage'
GO

INSERT INTO CellCarrier (Name,Transform) VALUES
('T Mobile','{0}@tmomail.net'),
('Verizon','{0}@vtext.com'),
('AT&T','{0}@txt.att.net'),
('C Spire Wireless','{0}@cspire1.com'),
('Bluegrass Cellular','{0}@sms.bluecell.com'),
('Sprint','{0}@messaging.sprintpcs.com'),
('Cricket Wireless', '{0}@mms.cricketwireless.net'),
('Republic Wireless','{0}@text.republicwireless.com'),
('Google Fi','{0}@msg.fi.google.com')
GO

-- ----------------------- --
-- DefaultChannelTypes.sql --
-- ----------------------- --
UPDATE ValueList SET AltValue = Value WHERE GroupID = (SELECT ID FROM ValueListGroup WHERE Name = 'Unit')
GO

INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('RMS Voltage', 'RMS Voltage', 'Volts', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S2/S1)', 'Voltage Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S0/S1)', 'Voltage Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Flicker Pst', 'Short Term Flicker Perceptibility (Pst)', 'Per Unit', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage THD', 'Voltage THD', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Harmonic', 'Voltage Harmonic', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S2/S1)', 'Current Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S0/S1)', 'Current Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current THD', 'Current THD', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Harmonic', 'Current Harmonic', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Active Power', 'Active Power', 'KW', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Reactive Power', 'Reactive Power', 'KVAR', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Apparent Power', 'Apparent Power', 'KVA', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Power Factor', 'Power Factor', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF'))
GO

/* UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V RMS'
GO
UPDATE ChannelGroupType SET Unit = 'Pst' WHERE DisplayName = 'V TotalTHD'
GO
UPDATE ChannelGroupType SET Unit = 'Pst' WHERE DisplayName = 'V FlkrPST'
GO
UPDATE ChannelGroupType SET Unit = 'Plt' WHERE DisplayName = 'V FlkrPLT'
GO
UPDATE ChannelGroupType SET Unit = 'Unkown' WHERE DisplayName = 'V SpectraHGroup'
GO
UPDATE ChannelGroupType SET Unit = 'Degree' WHERE DisplayName = 'V AngleFund'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'V AvgImbal'
GO
UPDATE ChannelGroupType SET Unit = 'Hz' WHERE DisplayName = 'V Frequency'
GO
UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V HRMS'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'V None'
GO
UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V SNeg'
GO
UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V SPos'
GO
UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V SZero'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'V S0S1'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'V S2S1'
GO
UPDATE ChannelGroupType SET Unit = 'Plt' WHERE DisplayName = 'V PLTSlide'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I RMS'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'I TotalTHD'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I SpectraHGroup'
GO
UPDATE ChannelGroupType SET Unit = 'Degree' WHERE DisplayName = 'I AngleFund'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'I AvgImbal'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I HRMS'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I SNeg'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I SPos'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I SZero'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'I S0S1'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'I S2S1'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'I KFactor'
GO
UPDATE ChannelGroupType SET Unit = 'VA' WHERE DisplayName = 'PIntg'
GO
UPDATE ChannelGroupType SET Unit = 'VA' WHERE DisplayName = 'QIntg'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'SIntgFund'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'AngleFund'
GO
UPDATE ChannelGroupType SET Unit = 'W' WHERE DisplayName = 'P'
GO
UPDATE ChannelGroupType SET Unit = 'W' WHERE DisplayName = 'PDemand'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'PF'
GO
UPDATE ChannelGroupType SET Unit = 'None' WHERE DisplayName = 'PFDemand'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'QDemand'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'QFund'
GO
UPDATE ChannelGroupType SET Unit = 'VA' WHERE DisplayName = 'S'
GO
UPDATE ChannelGroupType SET Unit = 'VA' WHERE DisplayName = 'SDemand'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'Q'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'DF'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'DFArith'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'DFVector'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'PFVector'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'PFArith'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'PHarmonic'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'SVector'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'SVectorFund'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'SArith'
GO
UPDATE ChannelGroupType SET Unit = 'VAR' WHERE DisplayName = 'SArithFund'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V CrestFactor'
GO
UPDATE ChannelGroupType SET Unit = 'V' WHERE DisplayName = 'V Peak'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'V EvenTHD'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'V OddTHD'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V FormFactor'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V ArithSum'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V TIF'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V Spectra'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'V SpectraIGroup'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I CrestFactor'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'IHRMS'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I IT'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I TID'
GO
UPDATE ChannelGroupType SET Unit = 'A' WHERE DisplayName = 'I Peak'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'I EvenTHD'
GO
UPDATE ChannelGroupType SET Unit = '%' WHERE DisplayName = 'I OddTHD'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I FormFactor'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I ArithSum'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I Spectra'
GO
UPDATE ChannelGroupType SET Unit = 'Unknown' WHERE DisplayName = 'I SpectraIGroup'
GO */

SET IDENTITY_INSERT AssetType ON
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (9,'StationAux','Station Auxilary')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (10,'StationBattery','Station Battery')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (11,'Generation','Generation')
GO

UPDATE AssetRelationshipType SET JumpConnection = 'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Bus-Line'
GO
UPDATE AssetRelationshipType SET JumpConnection = 'SELECT CASE WHEN MeasurementType.Name IN (''Voltage'', ''Digital'') THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Bus-Breaker'
GO
UPDATE AssetRelationshipType SET JumpConnection = 'SELECT CASE WHEN MeasurementType.Name IN (''Voltage'', ''Current'', ''Digital'') THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Line-(Single)Breaker'
GO
UPDATE AssetRelationshipType SET JumpConnection = 'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Line-(Double)Breaker'
GO
UPDATE AssetRelationshipType SET PassThrough = 'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Line-(Double)Breaker'
GO
UPDATE AssetRelationshipType SET JumpConnection = 'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}' WHERE Name = 'Bus-CapBank'
GO

INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Transformer-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name IN (''Voltage'', ''Current'', ''Digital'') THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('CapBank-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name IN (''Voltage'', ''Current'', ''Digital'') THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Bus-Transformer','only Voltages are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Line-Transformer','only Voltages are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Line-CapBank','only Voltages are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('DER-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name IN (''Voltage'', ''Current'', ''Digital'') THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Line-DER','only Voltages are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('DER-Transformer','only Voltages are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Transformer-(Double)Breaker','only Voltage are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
	VALUES ('Bus-(Double)Breaker','only Voltage are passed across this connection.',1,'SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}','SELECT CASE WHEN MeasurementType.Name = ''Voltage'' THEN 1 ELSE 0 END FROM Channel JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE Channel.ID = {ChannelID}')
GO

INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'CapBank-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-CapBank'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'DER')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-DER'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'DER')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Transformer'))
GO

INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'CapBank-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-CapBank'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-DER'),(SELECT ID FROM AssetType WHERE Name = 'DER')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Double)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker'))
GO

INSERT INTO EventTypeAssetType (EventTypeID,AssetTypeID) VALUES
	((SELECT ID FROM EventType WHERE Name = 'Fault'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM EventType WHERE Name = 'Fault'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
	((SELECT ID FROM EventType WHERE Name = 'RecloseIntoFault'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM EventType WHERE Name = 'BreakerOpen'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM EventType WHERE Name = 'Interruption'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM EventType WHERE Name = 'Sag'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM EventType WHERE Name = 'Swell'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM EventType WHERE Name = 'Transient'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM EventType WHERE Name = 'Interruption'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM EventType WHERE Name = 'Sag'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM EventType WHERE Name = 'Swell'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM EventType WHERE Name = 'Transient'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM EventType WHERE Name = 'Snapshot'),(SELECT ID FROM AssetType WHERE Name = 'Bus')),
	((SELECT ID FROM EventType WHERE Name = 'Snapshot'),(SELECT ID FROM AssetType WHERE Name = 'Breaker'))
GO


ALTER PROCEDURE [dbo].[selectSitesTrendingDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    declare @theDate as Date
    declare  @MeterIDs TABLE (ID int);

    set @theDate = CAST(@EventDate as Date)

    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

        Select
        Meter.ID as meterid,
        Channel.ID as channelid,
        Meter.Name as sitename,
        'Alarm' as eventtype,
        [dbo].[MeasurementCharacteristic].[Name] as characteristic,
        [dbo].[MeasurementType].[Name] as measurementtype,
        [dbo].[Phase].[Name] as phasename,
        Channel.HarmonicGroup,
        SUM (ChannelAlarmSummary.AlarmPoints) as eventcount,
        @theDate as date

        from Channel

        join ChannelAlarmSummary on ChannelAlarmSummary.ChannelID = Channel.ID and Date = @theDate
        join Meter on Channel.MeterID = Meter.ID and [MeterID] in ( Select * from @MeterIDs)

        join [dbo].[MeasurementCharacteristic] on Channel.MeasurementCharacteristicID = [dbo].[MeasurementCharacteristic].[ID]
        join [dbo].[MeasurementType] on Channel.MeasurementTypeID =  [dbo].[MeasurementType].ID
        join [dbo].[Phase] on Channel.PhaseID = [dbo].[Phase].ID

        Group By Meter.ID , Channel.ID , Meter.Name , [MeasurementCharacteristic].[Name] , [MeasurementType].[Name] , [dbo].[Phase].[Name], Channel.HarmonicGroup
        Order By Meter.ID

END
GO

ALTER PROCEDURE [dbo].[selectTrendingForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterID, ',')

SELECT AlarmDate as thedate, COALESCE(OffNormal,0) as Offnormal, COALESCE(Alarm,0) as Alarm
FROM(
    SELECT Date AS AlarmDate, 'Alarm' Name, SUM(AlarmPoints) as AlarmPoints
    FROM ChannelAlarmSummary JOIN
         Channel ON ChannelAlarmSummary.ChannelID = Channel.ID
    WHERE MeterID IN (SELECT * FROM #selectedMeters) AND Date >= @startDate AND Date < @endDate
    GROUP BY Date
) AS table1
PIVOT(
    SUM(table1.AlarmPoints)
    FOR table1.Name IN(Alarm, OffNormal)
) as pvt

END
GO

-- ---------------- --
-- SystemCenter.sql --
-- ---------------- --
ALTER TABLE [SystemCenter.Setting] ADD UNIQUE (Name)
GO

EXEC sp_rename '[SystemCenter.AdditionalUserField]', 'AdditionalUserField'
GO

EXEC sp_rename '[SystemCenter.AdditionalUserFieldValue]', 'AdditionalUserFieldValue'
GO

CREATE Table [ExternalDatabases] (
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Schedule varchar(50) NULL DEFAULT(NULL),
    ConnectionString Varchar(MAX) NOT NULL,
    DataProviderString VARCHAR(MAX) NULL,
    Encrypt bit NOT NULL DEFAULT(0),
    LastDataUpdate DATETIME2 NULL DEFAULT(NULL),
    Constraint UC_ExternalDatabase UNIQUE(Name)
)

CREATE Table [extDBTables] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	TableName varchar(200) NOT NULL,
    ExtDBID INT NOT NULL FOREIGN KEY References [ExternalDatabases](ID),
	Query varchar(max) NOT NULL,
    Constraint UC_ExternalDatabaseTable UNIQUE(TableName, ExtDBID)
)
GO

ALTER TABLE AdditionalField Add
	ExternalDBTableID INT NULL FOREIGN KEY REFERENCES [extDBTables](ID) DEFAULT(1),
	IsInfo bit NOT NULL DEFAULT(0),
    IsKey bit NOT NULL DEFAULT(0)
GO

CREATE TRIGGER [dbo].[UO_AdditonaFieldValue] 
ON [dbo].[AdditionalFieldValue]
AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE original SET UpdatedOn = SYSDATETIME()
		FROM dbo.[AdditionalFieldValue] as original INNER JOIN deleted ON 
		original.ID = deleted.ID AND 
		(
			original.Value <> deleted.Value OR
			(original.Value IS NULL AND deleted.Value IS NOT NULL) OR
			(original.Value IS NOT NULL AND deleted.Value IS NULL)
		);
END
GO

ALTER TABLE ExternalOpenXDAField ADD ExternalDBTableID INT NOT NULL FOREIGN KEY References [extDBTables](ID) DEFAULT(1)
GO

ALTER LocationDrawing Add
	Number VARCHAR(200) NULL,
    Category VARCHAR(max) NULL
GO

CREATE TABLE [LSCVSAccount] (
    ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    AccountID VARCHAR(200) NOT NULL,
    ChannelID int NOT NULL DEFAULT(1),
    CustomerID int NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(ID)
)
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('PQBrowser.Url', 'http://localhost', 'http://localhost')
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('XDA.Url', 'http://localhost:8989', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('XDA.APIKey', '', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('XDA.APIToken', '', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('XDA.ClientID', 'LocalXDAClient', 'LocalXDAClient')
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('FAWG.Enabled', 'False', 'False')
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.Url', 'http://localhost:8989', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.APIKey', '', '')
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) VALUES('MiMD.APIToken', '', '')
GO

Insert into ValueListGroup (Name, Description) VALUES('SpareChannel', 'List of spare channel names and descriptions for filtering in new meter wizard, case insensitive.')
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Virtual Spare',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare Virtual',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Current Spare',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare Current',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Voltage Spare',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare Voltage',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare Trigger',0)
GO
Insert into ValueList (GroupID, Value, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'SpareChannel'), 'Spare Channel',0)
GO